﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using JKF.DataModule.IDAL;
using JKF.Substructure;
using JKF.Entities.Model;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace JKF.DataModule.SqlServerDAL
{
    public partial class dalSHOP : IdalSHOP
    {
        // [dbo].[getDic_AreaAllName] 自定义的函数，用于获取省市县
        private const string FieldShow = "SHOP_CODE, SHOP_NAME, LINKMAN, PHONE, [dbo].[getDic_AreaAllName](AREA_CODE) as AREA_CODE , ADDRESS, POSTAL_CODE, MEMO, SMS_AVAIBLE, SMS_SENDED, SINGNATURE, USER_CREATE, DATE_CREATE, DATE_UPDATE";
        private const string FieldOrder = "DATE_CREATE DESC,SHOP_CODE,SHOP_NAME";
        private const string FieldKey = "SHOP_CODE";
        /// <summary>
        ///  获取Shop表中的数据
        /// </summary>
        /// <param name="userInfo">当前用户角色</param>
        /// <param name="SHOP_NAME">根据店面名称查询</param>
        /// <param name="LINKMAN">根据联系人查询</param>
        /// <param name="AREA_CODE">根据店面所在地区</param>
        /// <param name="pageCurrent">哪页</param>
        /// <param name="pageSize">页码大小</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="rowCount">记录总条数</param>
        /// <returns>返回DataTable</returns>
        public static DataTable GetShopsDAL(SYS_USERinfo userInfo, string SHOP_NAME, string LINKMAN, string AREA_CODE, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            var sql = new StringBuilder();
            //SYS_ROLEinfo roleInfo = new dalSYS_ROLE().GetModel(userInfo.ROLE_KEY);
            #region 错误的思路
            //            if (roleInfo.ROLE_NAME.Contains(SysConfig.HeadShop))
            //            {
            //                sql.AppendFormat(" 1=1  ");
            //            }
            //            else if (roleInfo.ROLE_NAME.Contains(SysConfig.AreaShop))
            //            {

            //                sql.AppendFormat(string.Format(@" shop_code in(  select b.shop_code 
            //from   dbo.SYS_USER a
            //inner join sys_user_shop b on a.user_key=b.user_key
            //where b.user_key='{0}')", userInfo.USER_KEY));
            //            } 
            #endregion

            //设定初始条件
            sql.AppendFormat(" shop_code in (select shop_code from dbo.SYS_USER_SHOP where user_key='{0}') ",userInfo.USER_KEY.ToString());

            // 下面组装查询条件
            if (!string.IsNullOrEmpty(SHOP_NAME))
            {
                sql.AppendFormat(" AND SHOP_NAME  LIKE '%{0}%'", SHOP_NAME);
            }
            if (!string.IsNullOrEmpty(LINKMAN))
            {
                sql.AppendFormat(" and LINKMAN LIKE '%{0}%'", LINKMAN);
            }
            if (!string.IsNullOrEmpty(AREA_CODE) && AREA_CODE != "-1")
            {
                // 这里需要重新处理
                switch (AREA_CODE.TrimEnd('0').Length)
                {
                    case 2:
                        sql.AppendFormat(" and AREA_CODE LIKE '{0}____'", AREA_CODE.TrimEnd('0'));
                        break;
                    case 4:
                        sql.AppendFormat(" and AREA_CODE LIKE '{0}__'", AREA_CODE.TrimEnd('0'));
                        break;
                    case 6:
                        sql.AppendFormat(" and AREA_CODE LIKE '{0}'", AREA_CODE.TrimEnd('0'));

                        break;
                }
                //sql.AppendFormat(" and AREA_CODE LIKE '%{0}%'", AREA_CODE);
            }
            // 下面调用此方法去查询数据库
            return DataPage.GetPageInfo("dbo.SHOP", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];

        }

        /// <summary>
        /// 从数据库中获取店面编码，保证不重复
        /// </summary>
        /// <returns></returns>
        public static string GetShopCodeDAL()
        {
            object obj = DB.HR.ExecuteScalar(CommandType.Text, @"SELECT TOP 1 CODE FROM [DIC_SHOP_CODE] WHERE CODE NOT IN (SELECT SHOP_CODE FROM SHOP)");
            return Convert.ToString(obj);
        }
        /// <summary>
        /// 判断是否存在此名称店面,true表示存在
        /// </summary>
        /// <param name="shop_name">店面名称</param>
        /// <returns></returns>
        public static bool ExistsShopNameDAL(string shop_name)
        {
            object obj = DB.HR.ExecuteScalar(CommandType.Text, "select count(1) from dbo.SHOP where SHOP_NAME='" + shop_name + "'");
            return Convert.ToInt32(obj) > 0;
        }
        /// <summary>
        /// 向店面插入数据
        /// </summary>
        /// <param name="shop_code">店面编码</param>
        /// <param name="area_code">地区编码</param>
        /// <param name="sql">sql语句</param>
        /// <param name="now_user_key">当前操作员ID</param>
        /// <returns>返回执行结果，返回1表示成功</returns>
        public static string TransactionSQLAddDAL(string shop_code,string area_code,string sql,string now_user_key)
        {
            #region 错误
            //采用事务提交
            //SqlParameter[] parameter = { 
            //        new SqlParameter("@insert_shop", SqlDbType.VarChar,1000), // 执行插入SHOP语句
            //        new SqlParameter("@now_user_key", SqlDbType.UniqueIdentifier,16),// 当前用户ID 
            //        new SqlParameter("@result", SqlDbType.Int,8)
            //                           };
            //parameter[0].Value = sql;
            //parameter[1].Value = now_user_key;
            //parameter[2].Direction = ParameterDirection.Output;
            //DB.HR.ExecuteNonQuery("proc_User_ShopUpdate", parameter);
            //string result = parameter[2].Value.ToString(); 
            #endregion

            int result = -1;
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_User_ShopAdd]");
            db.AddInParameter(dbCommand, "@insert_shop", DbType.String, sql);
            db.AddInParameter(dbCommand, "@shop_code", DbType.String, shop_code);
            db.AddInParameter(dbCommand, "@area_code", DbType.String, area_code);
            db.AddInParameter(dbCommand, "@now_user_key", DbType.Guid, new Guid(now_user_key));
            db.AddOutParameter(dbCommand, "@result", DbType.Int32, result);
            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["@result"].Value.ToString();
        }
        /// <summary>
        /// 更新店面数据
        /// </summary>
        /// <param name="shop_code">店面编码</param>
        /// <param name="area_code">地区编码</param>
        /// <param name="sql">sql语句</param>
        /// <param name="now_user_key">当前操作员ID</param>
        /// <returns>返回执行结果，返回1表示成功</returns>
        public static string TransactionSQLUpdateDAL(string shop_code, string area_code, string sql, string now_user_key)
        {
            int result = -1;
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_User_ShopUpdate]");
            db.AddInParameter(dbCommand, "@update_shop", DbType.String, sql);
            db.AddInParameter(dbCommand, "@shop_code", DbType.String, shop_code);
            db.AddInParameter(dbCommand, "@area_code", DbType.String, area_code);
            db.AddInParameter(dbCommand, "@now_user_key", DbType.Guid, new Guid(now_user_key));
            db.AddOutParameter(dbCommand, "@result", DbType.Int32, result);
            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["@result"].Value.ToString();
        }
        /// <summary>
        /// 事务删除店面
        /// </summary>
        /// <param name="shop_code">店面编码</param>
        /// <returns></returns>
        public static string TransactionSQLDeleteDAL(string shop_code)
        {
            int result = -1;
            Database db = DB.HR;
            DbCommand dbCommand = db.GetStoredProcCommand("[dbo].[proc_ShopDelete]");
            db.AddInParameter(dbCommand, "@SHOP_CODE", DbType.String, shop_code);
            db.AddOutParameter(dbCommand, "@result", DbType.Int32, result);
            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["@result"].Value.ToString();
        }
    }
}
